package com.mycompany.inventory.persistence;

import com.mycompany.common.AbstractDao;
import com.mycompany.common.jdbc.ConnectionDatabase;
import com.mycompany.inventory.pojo.OrderDetails;

import java.util.ArrayList;
import java.util.List;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Created by My Company.
 * User: loitran
 * Date: Feb 29, 2012
 * Time: 1:53:19 PM
 * com.mycompany.inventory.persistence.
 */
public class OrderDetailsDAO extends AbstractDao<OrderDetails>{
	
	private Connection con = this.connect;
	public static final String SELECT_ALL_SQL = "select * from oce_order_detail";
	public static final String SELECT_SQL = "select * from oce_order_detail where orderDetailId = ?";
	public static final String SELECT_BY_PRODUCT_ID_SQL = "select * from oce_order_detail where productId = ? and orderId = ? AND onOrder = ?";
	public static final String SELECT_QUANTITY_UNSTOCK_SQL = "select * from oce_order_detail where productId = ? and orderId = ? AND onOrder = ? AND receive_status = ?";
	public static final String SELECT_COUNT_RECEIVED_SQL = "select * from oce_order_detail where orderId = ? AND onOrder = ? AND receive_status = ?";
	public static final String SELECT_TOTAL_PAYMENT_SQL = "select * from oce_order_detail where orderId = ? AND onOrder = ?";
	public static final String SELECT_QUANTITY_SQL = "select * from oce_order_detail where orderId = ?";
	public static final String INSERT_SQL = "INSERT INTO oce_order_detail (orderDetailId,orderId,productId,quantity,discount) VALUES (?,?,?,?,?)";
	public static final String INSERT_RECEIVE_SQL = "INSERT INTO oce_order_detail (orderDetailId,orderId,productId,quantity_received,locationId,received_date,onOrder) VALUES (?,?,?,?,?,?,?)";
	public static final String UPDATE_SQL = "UPDATE oce_order_detail SET quantity = ?, discount = ? WHERE productId = ? and orderId = ?";
	public static final String UPDATE_RECEIVE_SQL = "UPDATE oce_order_detail SET quantity = ?, quantity_received =?, locationId = ?, received_date = ? WHERE productId = ? and orderId = ? and onOrder = ?";
	public static final String UPDATE_RETURN_SQL = "UPDATE oce_order_detail SET quantity_return =? WHERE productId = ? and orderId = ? and receive_status = ? and onOrder = ?";
	public static final String UPDATE_STATUS_RECEIVE_SQL = "UPDATE oce_order_detail SET receive_status = ? WHERE productId = ? and orderId = ? and onOrder = ?";
	public static final String INSERT_RETURN_SQL = "INSERT INTO oce_order_detail (orderDetailId,orderId,productId,quantity_return,receive_status,onOrder) VALUES (?,?,?,?,?,?)";
	public static final String INSERT_UNSTOCK_SQL = "INSERT INTO oce_order_detail (orderDetailId,orderId,productId,quantity_unstock,locationId,receive_status,onOrder) VALUES (?,?,?,?,?,?,?)";
	public static final String UPDATE_UNSTOCK_SQL = "UPDATE oce_order_detail SET quantity_unstock =?,locationId = ? WHERE productId = ? and orderId = ? and receive_status = ? and onOrder = ?";
	
	public OrderDetails create(OrderDetails obj){
		try{
			PreparedStatement ps = con.prepareStatement(INSERT_SQL, PreparedStatement.RETURN_GENERATED_KEYS);	
			long id = 0l;
			ps.setLong(1,id);
			ps.setString(2, obj.getOrderId());
			ps.setLong(3, Long.parseLong(obj.getProductId()));
			ps.setLong(4, Long.parseLong(obj.getQuantity()));
			ps.setLong(5, Long.parseLong(obj.getDiscount()));
			ConnectionDatabase.executeUpdate(ps);	
			ResultSet rs=ps.getGeneratedKeys();
            if(rs.next()){
                Integer Id = (Integer) rs.getInt(1);               
                obj.setId(String.valueOf(Id));
            }
			ps.close();			
			
		}catch (Exception e) {
			// TODO: handle exception
			obj = null;
		}
		return obj;
	}
	
	public OrderDetails createReceive(OrderDetails obj){
		try{
			PreparedStatement ps = con.prepareStatement(INSERT_RECEIVE_SQL, PreparedStatement.RETURN_GENERATED_KEYS);	
			long id = 0l;
			ps.setLong(1,id);
			ps.setString(2, obj.getOrderId());
			ps.setLong(3, Long.parseLong(obj.getProductId()));
			ps.setLong(4, obj.getQuantityReceived());
			ps.setLong(5, Long.parseLong(obj.getLocationId()));
			ps.setDate(6, obj.getReceivedDate());
			ps.setLong(7, obj.getOnOrder());
			ConnectionDatabase.executeUpdate(ps);	
			ResultSet rs=ps.getGeneratedKeys();
            if(rs.next()){
                Integer Id = (Integer) rs.getInt(1);               
                obj.setId(String.valueOf(Id));
            }
			ps.close();			
			
		}catch (Exception e) {
			// TODO: handle exception
			obj = null;
		}
		return obj;
	}	
	
	public OrderDetails update(OrderDetails obj){
		try{
			PreparedStatement ps = con.prepareStatement(UPDATE_SQL, PreparedStatement.RETURN_GENERATED_KEYS);	
			ps.setLong(1, Long.parseLong(obj.getQuantity()));
			ps.setLong(2, Long.parseLong(obj.getDiscount()));
			ps.setLong(3, Long.parseLong(obj.getProductId()));
			ps.setString(4, obj.getOrderId());
			
			ConnectionDatabase.executeUpdate(ps);
			ps.close();
		}catch (Exception e) {
			// TODO: handle exception
			obj = null;
		}
		return obj;
	}

	public OrderDetails updateReceive(OrderDetails obj){
		try{
			PreparedStatement ps = con.prepareStatement(UPDATE_RECEIVE_SQL, PreparedStatement.RETURN_GENERATED_KEYS);	
			ps.setLong(1, Long.parseLong(obj.getQuantity()));
			ps.setLong(2, obj.getQuantityReceived());
			ps.setLong(3, Long.parseLong(obj.getLocationId()));
			ps.setDate(4, obj.getReceivedDate());			
			ps.setLong(5, Long.parseLong(obj.getProductId()));
			ps.setString(6, obj.getOrderId());
			ps.setLong(7, obj.getOnOrder());
			
			ConnectionDatabase.executeUpdate(ps);
			ps.close();
		}catch (Exception e) {
			// TODO: handle exception
			obj = null;
		}
		return obj;
	}	

	public OrderDetails createReturn(OrderDetails obj){
		try{
			PreparedStatement ps = con.prepareStatement(INSERT_RETURN_SQL, PreparedStatement.RETURN_GENERATED_KEYS);	
			long id = 0l;
			ps.setLong(1,id);
			ps.setString(2, obj.getOrderId());
			ps.setLong(3, Long.parseLong(obj.getProductId()));
			ps.setLong(4, obj.getQuantityReturn());
			ps.setString(5, obj.getReceiveStatus());
			ps.setLong(6, obj.getOnOrder());
			ConnectionDatabase.executeUpdate(ps);	
			ResultSet rs=ps.getGeneratedKeys();
            if(rs.next()){
                Integer Id = (Integer) rs.getInt(1);               
                obj.setId(String.valueOf(Id));
            }
			ps.close();			
			
		}catch (Exception e) {
			// TODO: handle exception
			obj = null;
		}
		return obj;
	}	
	
	public OrderDetails updateReturn(OrderDetails obj){
		try{
			PreparedStatement ps = con.prepareStatement(UPDATE_RETURN_SQL, PreparedStatement.RETURN_GENERATED_KEYS);	
			ps.setLong(1, obj.getQuantityReturn());
			ps.setLong(2, Long.parseLong(obj.getProductId()));
			ps.setString(3, obj.getOrderId());
			ps.setString(4, obj.getReceiveStatus());
			ps.setLong(5, obj.getOnOrder());
			ConnectionDatabase.executeUpdate(ps);
			ps.close();
		}catch (Exception e) {
			// TODO: handle exception
			obj = null;
		}
		return obj;
	}	
	
	public OrderDetails createUnstock(OrderDetails obj){
		try{
			PreparedStatement ps = con.prepareStatement(INSERT_UNSTOCK_SQL, PreparedStatement.RETURN_GENERATED_KEYS);	
			long id = 0l;
			ps.setLong(1,id);
			ps.setString(2, obj.getOrderId());
			ps.setLong(3, Long.parseLong(obj.getProductId()));
			ps.setLong(4, obj.getQuantityUnstock());
			ps.setLong(5, Long.parseLong(obj.getLocationId()));
			ps.setString(6, obj.getReceiveStatus());
			ps.setLong(7, obj.getOnOrder());
			ConnectionDatabase.executeUpdate(ps);	
			ResultSet rs=ps.getGeneratedKeys();
            if(rs.next()){
                Integer Id = (Integer) rs.getInt(1);               
                obj.setId(String.valueOf(Id));
            }
			ps.close();			
			
		}catch (Exception e) {
			// TODO: handle exception
			obj = null;
		}
		return obj;
	}	
	
	public OrderDetails updateUnstock(OrderDetails obj){
		try{
			PreparedStatement ps = con.prepareStatement(UPDATE_UNSTOCK_SQL, PreparedStatement.RETURN_GENERATED_KEYS);	
			ps.setLong(1, obj.getQuantityUnstock());
			ps.setLong(2, Long.parseLong(obj.getLocationId()));
			ps.setLong(3, Long.parseLong(obj.getProductId()));
			ps.setString(4, obj.getOrderId());
			ps.setString(5, obj.getReceiveStatus());
			ps.setLong(6, obj.getOnOrder());
			ConnectionDatabase.executeUpdate(ps);
			ps.close();
		}catch (Exception e) {
			// TODO: handle exception
			obj = null;
		}
		return obj;
	}	
	
	public OrderDetails updateStatus(OrderDetails obj){
		try{
			PreparedStatement ps = con.prepareStatement(UPDATE_STATUS_RECEIVE_SQL, PreparedStatement.RETURN_GENERATED_KEYS);	
			ps.setString(1, obj.getReceiveStatus());
			ps.setLong(2, Long.parseLong(obj.getProductId()));
			ps.setString(3, obj.getOrderId());
			ps.setLong(4, obj.getOnOrder());
			
			ConnectionDatabase.executeUpdate(ps);
			ps.close();
		}catch (Exception e) {
			// TODO: handle exception
			obj = null;
		}
		return obj;
	}	
	
	public OrderDetails find(long id){
		List<OrderDetails> orderDetails = new ArrayList<OrderDetails>();
		try{
			PreparedStatement ps = con.prepareStatement(SELECT_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setLong(1,id);
			orderDetails = convert(ConnectionDatabase.executeQuery(ps));
		}catch (Exception e) {
			// TODO: handle exception
		}
		return orderDetails.size()== 0 ? null : orderDetails.get(0);
	}
	
	public List<OrderDetails> findAll(){
		List<OrderDetails> orderDetails = new ArrayList<OrderDetails>();
		try{
			orderDetails = convert(ConnectionDatabase.executeQuery(con.prepareStatement(SELECT_ALL_SQL, PreparedStatement.RETURN_GENERATED_KEYS)));
		}catch (Exception e) {
			// TODO: handle exception
			orderDetails = null;
		}
		return orderDetails;
	}

	public List<OrderDetails> findByProductId(long productId, String orderId,int onOrder){
		List<OrderDetails> orderDetails = new ArrayList<OrderDetails>();
		try{
			PreparedStatement ps = con.prepareStatement(SELECT_BY_PRODUCT_ID_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setLong(1,productId);
			ps.setString(2,orderId);
			ps.setLong(3, onOrder);
			orderDetails = convert(ConnectionDatabase.executeQuery(ps));
		}catch (Exception e) {
			// TODO: handle exception
			orderDetails = null;
		}
		return orderDetails;
	}	

	public List<OrderDetails> getQuantityUnstocks(long productId, String orderId,String onOrder, String istatus){
		List<OrderDetails> orderDetails = new ArrayList<OrderDetails>();
		try{
			PreparedStatement ps = con.prepareStatement(SELECT_QUANTITY_UNSTOCK_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setLong(1,productId);
			ps.setString(2,orderId);
			ps.setString(3, onOrder);
			ps.setString(4, istatus);
			orderDetails = convert(ConnectionDatabase.executeQuery(ps));
		}catch (Exception e) {
			// TODO: handle exception
			orderDetails = null;
		}
		return orderDetails;
	}
	
	public List<OrderDetails> getCountReceived(String orderId,String onOrder, String istatus){
		List<OrderDetails> orderDetails = new ArrayList<OrderDetails>();
		try{
			PreparedStatement ps = con.prepareStatement(SELECT_COUNT_RECEIVED_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setString(1,orderId);
			ps.setString(2, onOrder);
			ps.setString(3, istatus);
			orderDetails = convert(ConnectionDatabase.executeQuery(ps));
		}catch (Exception e) {
			// TODO: handle exception
			orderDetails = null;
		}
		return orderDetails;
	}
	
	public List<OrderDetails> getTotalPayment(String orderId,String onOrder){
		List<OrderDetails> orderDetails = new ArrayList<OrderDetails>();
		try{
			PreparedStatement ps = con.prepareStatement(SELECT_TOTAL_PAYMENT_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setString(1,orderId);
			ps.setString(2, onOrder);
			orderDetails = convert(ConnectionDatabase.executeQuery(ps));
		}catch (Exception e) {
			// TODO: handle exception
			orderDetails = null;
		}
		return orderDetails;
	}		
	
	public List<OrderDetails> findProductForOrders(long orderId){
		List<OrderDetails> orderDetails = new ArrayList<OrderDetails>();
		try{
			PreparedStatement ps = con.prepareStatement(SELECT_QUANTITY_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setLong(1,orderId);
			orderDetails = convert(ConnectionDatabase.executeQuery(ps));
		}catch (Exception e) {
			// TODO: handle exception
			orderDetails = null;
		}
		return orderDetails;
	}	
	
	public void delete(OrderDetails obj){
		try{
			
		}catch (Exception e) {
			// TODO: handle exception
		}
	}
	
	private List<OrderDetails> convert(ResultSet rs) throws SQLException {
		List<OrderDetails> orderDetails = new ArrayList<OrderDetails>();
		while (rs.next()) {
	        // Get the data from the row using the column index
	        String id = rs.getString(1);
	        String orderId = rs.getString(2);
	        String productId = rs.getString(3);
	        String quantity = rs.getString(4);
	        String discount = rs.getString(5);
	        int qReceive = rs.getInt(6);
	        int returnQ = rs.getInt(7);
	        int unstock = rs.getInt(8);
	        String locationId = rs.getString(9);
	        Date receiveDate = rs.getDate(10);
	        String status = rs.getString(11);
	        int onOrder = rs.getInt(12);
	        orderDetails.add(new OrderDetails(id,orderId,productId,quantity,discount,qReceive,returnQ,unstock,locationId,receiveDate,status,onOrder));
	    }
		rs.close();
		return orderDetails;
	}
}
